﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Odbc;
using System.Collections;
using Salas = LI4SilverWCF.bus.Salas;

namespace LI4SilverWCF.data
{
    class DBSalas
    {
        OdbcConnection con;
        public DBSalas(OdbcConnection c)
        {
            con = c;
        }

        public Hashtable obterListaSalas(String cinema)
        {
            Hashtable lista = new Hashtable();
            string query = "SELECT COD_SALA, NUM_SALA FROM SALA INNER JOIN CINEMA ON SALA.COD_CINEMA = CINEMA.COD_CINEMA WHERE NOME_CINEMA ='" + cinema + "'";
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();

            try
            {
                while (reader.Read())
                {
                    lista.Add(reader.GetInt32(0), reader.GetInt32(1));
                }

            }
            catch (Exception e)
            {
            }
            con.Close();
            return lista;
        }

        public void obterDadosSala(int numero, Salas s)
        {
            string query = "SELECT COD_SALA, LUGARES "
                          + "FROM SALA INNER JOIN CINEMA ON SALA.COD_CINEMA = CINEMA.COD_CINEMA "
                          + "WHERE NUM_SALA = " + numero + " AND NOME_CINEMA = '" + s.nomeCinema + "'";
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();

            try
            {
                while (reader.Read())
                {
                    if (!reader.IsDBNull(0)) s.codigoSala = (int)reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) s.lugares = (int)reader.GetInt32(1);
                }

            }
            catch (Exception e)
            {
            }
            con.Close();

            ArrayList filme = new ArrayList();
            Hashtable hor = new Hashtable();
            query = "SELECT NOME_FILME, YEAR(DATA_ESTREIA), HORA, NUM_SALA FROM HORARIO_CINEMA_SALA_FILME INNER JOIN FILME ON HORARIO_CINEMA_SALA_FILME.COD_FILME = FILME.COD_FILME INNER JOIN SALA ON HORARIO_CINEMA_SALA_FILME.COD_SALA = SALA.COD_SALA WHERE NUM_SALA = " + numero;
            command = new OdbcCommand(query, con);
            con.Open();
            reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    filme.Add(reader.GetString(0));
                    filme.Add(reader.GetString(1));
                    hor.Add(reader.GetString(2), filme);
                }
            }
            catch (Exception e)
            {
            }
            con.Close();
            s.horarios = hor;
        }


        public void removerSala(int numero, String cinema)
        {
            string query = "DELETE HORARIO_CINEMA_SALA_FILME FROM HORARIO_CINEMA_SALA_FILME INNER JOIN SALA ON HORARIO_CINEMA_SALA_FILME.COD_SALA = SALA.COD_SALA INNER JOIN CINEMA ON HORARIO_CINEMA_SALA_FILME.COD_CINEMA = CINEMA.COD_CINEMA WHERE NUM_SALA = " + numero + " AND NOME_CINEMA = '" + cinema + "'";
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            int rows = 0;
            try
            {
                rows = command.ExecuteNonQuery();
                Console.WriteLine(rows);
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO REMOVER HORARIOS DA SALA: " + e);
            }
            con.Close();

            query = "DELETE SALA FROM SALA INNER JOIN CINEMA ON SALA.COD_CINEMA = CINEMA.COD_CINEMA WHERE NOME_CINEMA = '" + cinema + "' AND NUM_SALA = " + numero;
            command = new OdbcCommand(query, con);
            con.Open();
            rows = 0;
            try
            {
                rows = command.ExecuteNonQuery();
                Console.WriteLine(rows);
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO REMOVER SALA: " + e);
            }
            con.Close();
        }

        public bool verificaUnicidadeSala(int numero, String cinema)
        {
            string query = "SELECT COD_SALA FROM SALA INNER JOIN CINEMA ON SALA.COD_CINEMA = CINEMA.COD_CINEMA WHERE NUM_SALA = " + numero + " AND NOME_CINEMA = '" + cinema + "'";
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();
            bool unico = false;

            try
            {
                if (!reader.HasRows) unico = true;
            }
            catch (Exception e)
            {
            }
            con.Close();
            return unico;
        }

        public void guardarNumeroSala(int numero, String cinema)
        {
            string query = "SELECT COD_CINEMA FROM CINEMA WHERE NOME_CINEMA = '" + cinema + "'";
            int cod_cinema = 0;
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    if (!reader.IsDBNull(0)) cod_cinema = (int)reader.GetInt32(0);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO VERIFICAR CINEMA (SALA): " + e);
            }
            con.Close();

            query = "INSERT INTO SALA (COD_CINEMA, NUM_SALA, LUGARES) VALUES (" + cod_cinema + "," + numero + ",0)";
            command = new OdbcCommand(query, con);
            con.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO INSERIR SALA: " + e);
            }
            con.Close();
        }

        public void inserirDadosSala(int numero, String cinema, int lugares, Hashtable horarios) //horario contem filme(nome e ano) e hora
        {
            string query = "SELECT COD_CINEMA FROM CINEMA WHERE NOME_CINEMA = '" + cinema + "'";
            int cod_cinema = 0;
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    if (!reader.IsDBNull(0)) cod_cinema = (int)reader.GetInt32(0);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO VERIFICAR CINEMA (SALA): " + e);
            }
            con.Close();

            query = "SELECT COD_SALA FROM SALA WHERE NUM_SALA = '" + numero + "'";
            int cod_sala = 0;
            command = new OdbcCommand(query, con);
            con.Open();
            reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    if (!reader.IsDBNull(0)) cod_sala = (int)reader.GetInt32(0);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO VERIFICAR CODIGO SALA: " + e);
            }
            con.Close();

            query = "UPDATE SALA SET LUGARES = " + lugares + " WHERE NUM_SALA = " + numero + " AND COD_CINEMA = " + cod_cinema;
            command = new OdbcCommand(query, con);
            con.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO GUARDAR DADOS SALA: " + e);
            }
            con.Close();

            query = "DELETE FROM HORARIO_CINEMA_SALA_FILME WHERE COD_SALA = " + cod_sala + " AND COD_CINEMA = " + cod_cinema;
            command = new OdbcCommand(query, con);
            con.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO GUARDAR DADOS SALA: " + e);
            }
            con.Close();

            ArrayList filme;
            IDictionaryEnumerator en = horarios.GetEnumerator();
            while (en.MoveNext())
            {

                filme = (ArrayList)en.Value;
                query = "SELECT COD_FILME FROM FILME WHERE NOME_FILME = '" + filme[0] + "' AND YEAR(DATA_ESTREIA) =" + filme[1];
                int cod_filme = 0;
                command = new OdbcCommand(query, con);
                con.Open();
                reader = command.ExecuteReader();
                try
                {
                    while (reader.Read())
                    {
                        if (!reader.IsDBNull(0)) cod_filme = (int)reader.GetInt32(0);
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine("ERRO AO VERIFICAR FILME (SALA): " + e);
                }
                con.Close();
                query = "INSERT INTO HORARIO_CINEMA_SALA_FILME (COD_CINEMA,COD_SALA,COD_FILME,HORA) VALUES (" + cod_cinema + ", " + cod_sala + ", " + cod_filme + ", '" + en.Key + "')";
                command = new OdbcCommand(query, con);
                con.Open();
                try
                {
                    command.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    Console.WriteLine("ERRO AO GUARDAR HORARIO: " + e);
                }
                con.Close();
            }

        }

    }
}